﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Text;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;

    public class RoleFunctionDA : IRoleFunctionDA
    {
        private string COMMONID = "@COMMONID";
        private string CREATED_BY = "@CREATED_BY";
        private string FUNCTION_ID = "@FUNCTION_ID";
        private string LANGUAGE = "@LANGUAGE";
        private string OPERATION_ID = "@OPERATION_ID";
        private string ROLE_ID = "@ROLE_ID";
        private string SQL_DELETE_ROLE_FUNCTION = " DELETE FROM SYSADM.ROLE_FUNCTION WHERE ROLE_ID = @ROLE_ID AND FUNCTION_ID = @FUNCTION_ID ";
        private string SQL_DELETE_ROLE_OPERATION = " DELETE FROM SYSADM.ROLE_OPERATION WHERE ROLE_ID = @ROLE_ID AND OPERATION_ID = @OPERATION_ID ";
        private string SQL_GET_ALL_FUNCTION_LIST = " SELECT URL FROM SYSADM.FUNCTION_LIST ";
        private string SQL_INSERT_ROLE_FUNCTION = " INSERT INTO SYSADM.ROLE_FUNCTION ( ROLE_ID, FUNCTION_ID, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @ROLE_ID, @FUNCTION_ID, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_INSERT_ROLE_OPERATION = " INSERT INTO SYSADM.ROLE_OPERATION ( ROLE_ID, OPERATION_ID, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @ROLE_ID, @OPERATION_ID, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string TYPE = "@TYPE";
        private string UPDATED_BY = "@UPDATED_BY";

        public int DeleteRoleFunction(DataBase dataBase, DbTransaction tran, List<string> ROLE_ID)
        {
            int result = 0;
            try
            {
                string StrSql = "DELETE FROM SYSADM.ROLE_FUNCTION WHERE ROLE_ID = @ROLE_ID";
                for (int i = 0; i < ROLE_ID.Count; i++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROLE_ID", ROLE_ID[i]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, StrSql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteRoleFunction(DataBase dataBase, DbTransaction tran, List<string> RoleFunctionID, string ROLE_ID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < RoleFunctionID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROLE_ID", ROLE_ID), new SqlParameter("@FUNCTION_ID", RoleFunctionID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ROLE_FUNCTION, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteRoleOperation(DataBase dataBase, DbTransaction tran, List<string> ROLE_ID)
        {
            int result = 0;
            try
            {
                string strSql = "DELETE FROM SYSADM.ROLE_OPERATION WHERE ROLE_ID = @ROLE_ID";
                for (int i = 0; i < ROLE_ID.Count; i++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROLE_ID", ROLE_ID[i]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteRoleOperation(DataBase dataBase, DbTransaction tran, List<string> RoleOperationID, string ROLE_ID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < RoleOperationID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROLE_ID", ROLE_ID), new SqlParameter("@OPERATION_ID", RoleOperationID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ROLE_OPERATION, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllFunctionList()
        {
            return DBHelper.ExecuteDataSet(CommandType.Text, this.SQL_GET_ALL_FUNCTION_LIST);
        }

        /// <summary>
        ///    修改bug：
        ///    string sql = " SELECT BUTTON_NAME FROM SYSADM.OPERATION_LIST WHERE OPERATION_ID IN ( SELECT DISTINCT OPERATION_ID FROM SYSADM.ROLE_OPERATION WHERE ROLE_ID IN (SELECT ROLE_ID FROM SYSADM.USER_ROLE WHERE USER_ID = @USER_ID) ) AND FUNCTION_ID = @FUNCTION_ID ";
        ///    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", userID), new SqlParameter("@FUNCTION_ID", functionID) };
        ///    return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="functionID"></param>
        /// <returns></returns>
        public DataSet GetOperationByUserIDAndFunctionID(string userID, string functionID)
        {
            string sql = " SELECT BUTTON_NAME FROM SYSADM.OPERATION_LIST WHERE cast(OPERATION_ID as varchar(20))  IN ( SELECT DISTINCT OPERATION_ID FROM SYSADM.ROLE_OPERATION WHERE ROLE_ID IN (SELECT ROLE_ID FROM SYSADM.USER_ROLE WHERE USER_ID = @USER_ID) ) AND FUNCTION_ID = @FUNCTION_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", userID), new SqlParameter("@FUNCTION_ID", functionID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetOperationByUserIDAndFunctionID(DataBase dataBase, DbTransaction tran, string userID, string functionID)
        {
            string sql = " SELECT BUTTON_NAME FROM SYSADM.OPERATION_LIST WHERE cast(OPERATION_ID as varchar(20))  IN ( SELECT DISTINCT OPERATION_ID FROM SYSADM.ROLE_OPERATION WHERE ROLE_ID IN (SELECT ROLE_ID FROM SYSADM.USER_ROLE WHERE USER_ID = @USER_ID) ) AND FUNCTION_ID = @FUNCTION_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", userID), new SqlParameter("@FUNCTION_ID", functionID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public DataSet GetRoleAndRoleFunctionDataByRoleId(string roleId)
        {
            StringBuilder StrSql = new StringBuilder();
            StrSql.Append(" SELECT SFG.GROUP_ID AS A, SFG1.GROUP_ID AS B,SFL.GROUP_ID AS C ,SFL.FUNCTION_ID AS D,SRO.OPERATION_ID AS E FROM SYSADM.FUNCTION_GROUP SFG  ");
            StrSql.Append(" LEFT JOIN SYSADM.FUNCTION_GROUP SFG1 ");
            StrSql.Append(" ON SFG.GROUP_ID = SFG1.PARENT_GROUP_GID");
            StrSql.Append(" LEFT JOIN SYSADM.FUNCTION_GROUP SFG2  ");
            StrSql.Append(" ON SFG1.GROUP_ID = SFG2.PARENT_GROUP_GID ");
            StrSql.Append(" LEFT JOIN SYSADM.FUNCTION_LIST SFL ");
            StrSql.AppendFormat("ON (SFG1.GROUP_ID = SFL.GROUP_ID  OR SFG2.GROUP_ID = SFL.GROUP_ID) AND  SFL.FUNCTION_ID IN (SELECT FUNCTION_ID FROM SYSADM.ROLE_FUNCTION WHERE ROLE_ID='{0}')", roleId);
            StrSql.Append("  LEFT JOIN SYSADM.OPERATION_LIST SRO ");
            //StrSql.AppendFormat("  ON SRO.FUNCTION_ID= SFL.FUNCTION_ID AND SRO.OPERATION_ID IN (SELECT OPERATION_ID FROM SYSADM.ROLE_OPERATION WHERE ROLE_ID='{0}') ", roleId);
            StrSql.AppendFormat("  ON SRO.FUNCTION_ID= SFL.FUNCTION_ID AND cast(SRO.OPERATION_ID as varchar(20)) IN (SELECT OPERATION_ID FROM SYSADM.ROLE_OPERATION WHERE ROLE_ID='{0}') ", roleId);
            StrSql.Append(" WHERE SFG.PARENT_GROUP_GID ='-1' ORDER BY  SFG.GROUP_ID");
            return DBHelper.ExecuteDataSet(CommandType.Text, StrSql.ToString());
        }

        public DataSet GetRoleFunctionData(string id, string language, string type)
        {
            DataSet CS10000;
            StringBuilder StrSql = new StringBuilder();
            StrSql.AppendFormat("  SELECT  FG.GROUP_ID AS COMMONID,T.[TEXT],FG.PARENT_GROUP_GID FROM SYSADM.FUNCTION_GROUP FG LEFT JOIN  SYSADM.TRANSLATION T ON FG.GROUP_ID = T.ITEM_ID AND  T.LANG+'-'+T.COUNTRY = '{0}'  AND T.ITEM_TYPE='FUNCTION_GROUP'  WHERE  FG.IS_ACTIVE='Y' ORDER BY FG.SEQ_NO  ", language);
            StrSql.AppendFormat("  SELECT FL.FUNCTION_ID AS COMMONID,T.[TEXT],FL.URL,FL.GROUP_ID FROM SYSADM.FUNCTION_LIST FL LEFT JOIN  SYSADM.TRANSLATION T ON FL.FUNCTION_ID = T.ITEM_ID AND T.ITEM_TYPE='FUNCTION_LIST' AND T.LANG+'-'+T.COUNTRY = '{0}'   ORDER BY SEQ_NO  ", language);
            StrSql.AppendFormat("  SELECT OL.OPERATION_ID AS COMMONID ,T.[TEXT],'' AS URL,OL.FUNCTION_ID  FROM SYSADM.OPERATION_LIST OL LEFT JOIN  SYSADM.TRANSLATION T ON OL.BUTTON_NAME = T.ITEM_ID AND T.ITEM_TYPE='BUTTON_LIST'  AND  T.LANG+'-'+T.COUNTRY = '{0}' ", language);
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter(this.COMMONID, SqlDbType.VarChar, 30), new SqlParameter(this.LANGUAGE, SqlDbType.VarChar, 10), new SqlParameter(this.TYPE, SqlDbType.VarChar, 5) };
                paras[0].Value = id;
                paras[1].Value = language;
                paras[2].Value = type;
                CS10000 = DBHelper.ExecuteDataSet(CommandType.Text, StrSql.ToString(), paras);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return CS10000;
        }

        public DataSet GetRoleMenuData(string userID, string language, string isShowQuery, string isGlobal)
        {
            DataSet CS10000;
            StringBuilder strSql = new StringBuilder();
            if (isGlobal == "Y")
            {
                strSql.Append(" SELECT DISTINCT FG.GROUP_ID,T.[TEXT],FG.PARENT_GROUP_GID,FL.URL, FL.FUNCTION_ID,FG.SEQ_NO  FROM SYSADM.FUNCTION_GROUP FG");
                strSql.Append(" LEFT JOIN SYSADM.FUNCTION_LIST FL ON FG.GROUP_ID = FL.GROUP_ID  AND");
                if (isShowQuery == "Y")
                {
                    strSql.Append(" FL.IS_ACTIVE='Y'");
                }
                else
                {
                    strSql.Append(" FL.IS_ROLE_CRTL ='Y' ");
                }
                strSql.AppendFormat("LEFT JOIN SYSADM.ROLE_FUNCTION  RF ON RF.FUNCTION_ID = FL.FUNCTION_ID  INNER JOIN SYSADM.TRANSLATION T ON FG.GROUP_ID = T.ITEM_ID AND T.ITEM_TYPE='FUNCTION_GROUP'  WHERE  T.LANG+'-'+T.COUNTRY = '{0}'  ORDER BY  FG.SEQ_NO  ", language);
                strSql.Append("  SELECT SFG.GROUP_ID AS A, SFG1.GROUP_ID AS B,SFL.GROUP_ID AS C, SFL.FUNCTION_ID AS D  FROM SYSADM.FUNCTION_GROUP SFG ");
                strSql.Append("  LEFT JOIN SYSADM.FUNCTION_GROUP SFG1  ");
                strSql.Append("  ON SFG.GROUP_ID = SFG1.PARENT_GROUP_GID  ");
                strSql.Append(" LEFT JOIN SYSADM.FUNCTION_GROUP SFG2  ");
                strSql.Append(" ON SFG1.GROUP_ID = SFG2.PARENT_GROUP_GID  ");
                strSql.Append(" LEFT JOIN SYSADM.FUNCTION_LIST SFL");
                strSql.Append(" ON (SFG1.GROUP_ID = SFL.GROUP_ID  OR SFG2.GROUP_ID = SFL.GROUP_ID) AND SFL.IS_GLOBAL='Y' AND SFL.IS_ACTIVE ='Y' ");
                strSql.AppendFormat(" AND SFL.FUNCTION_ID IN (SELECT FUNCTION_ID FROM SYSADM.ROLE_FUNCTION  WHERE ROLE_ID IN (SELECT ROLE_ID FROM SYSADM.USER_ROLE WHERE USER_ID ='{0}')) ", userID);
                strSql.Append(" WHERE SFG.PARENT_GROUP_GID ='-1'  AND SFL.GROUP_ID IS NOT NULL AND SFL.FUNCTION_ID IS NOT NULL ORDER BY  SFG.GROUP_ID ");
            }
            else
            {
                strSql.Append(" SELECT DISTINCT FG.GROUP_ID,T.[TEXT],FG.PARENT_GROUP_GID,FL.URL, FL.FUNCTION_ID,FG.SEQ_NO  FROM SYSADM.FUNCTION_GROUP FG");
                strSql.Append(" LEFT JOIN SYSADM.FUNCTION_LIST FL ON FG.GROUP_ID = FL.GROUP_ID  AND");
                if (isShowQuery == "Y")
                {
                    strSql.Append(" FL.IS_ACTIVE='Y'");
                }
                else
                {
                    strSql.Append(" FL.IS_ROLE_CRTL ='Y' ");
                }
                strSql.AppendFormat("LEFT JOIN SYSADM.ROLE_FUNCTION  RF ON RF.FUNCTION_ID = FL.FUNCTION_ID  INNER JOIN SYSADM.TRANSLATION T ON FG.GROUP_ID = T.ITEM_ID AND T.ITEM_TYPE='FUNCTION_GROUP'  WHERE  T.LANG+'-'+T.COUNTRY = '{0}'  ORDER BY  FG.SEQ_NO  ", language);
                strSql.Append("  SELECT SFG.GROUP_ID AS A, SFG1.GROUP_ID AS B,SFL.GROUP_ID AS C, SFL.FUNCTION_ID AS D  FROM SYSADM.FUNCTION_GROUP SFG ");
                strSql.Append("  LEFT JOIN SYSADM.FUNCTION_GROUP SFG1  ");
                strSql.Append("  ON SFG.GROUP_ID = SFG1.PARENT_GROUP_GID  ");
                strSql.Append(" LEFT JOIN SYSADM.FUNCTION_GROUP SFG2  ");
                strSql.Append(" ON SFG1.GROUP_ID = SFG2.PARENT_GROUP_GID  ");
                strSql.Append(" LEFT JOIN SYSADM.FUNCTION_LIST SFL");
                strSql.Append(" ON (SFG1.GROUP_ID = SFL.GROUP_ID  OR SFG2.GROUP_ID = SFL.GROUP_ID) AND SFL.IS_WAREHOUSE='Y' AND SFL.IS_ACTIVE ='Y' ");
                strSql.AppendFormat(" AND SFL.FUNCTION_ID IN (SELECT FUNCTION_ID FROM SYSADM.ROLE_FUNCTION  WHERE ROLE_ID IN (SELECT ROLE_ID FROM SYSADM.USER_ROLE WHERE USER_ID ='{0}')) ", userID);
                strSql.Append(" WHERE SFG.PARENT_GROUP_GID ='-1'  AND SFL.GROUP_ID IS NOT NULL AND SFL.FUNCTION_ID IS NOT NULL ORDER BY  SFG.GROUP_ID ");
            }
            try
            {
                CS10000 = DBHelper.ExecuteDataSet(CommandType.Text, strSql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return CS10000;
        }

        public DataSet GetWHDataByUserID(string userID)
        {
            StringBuilder StrSql = new StringBuilder();
            StrSql.AppendFormat("SELECT SD.WH_DB_ID,SD.WH_DB_NAME,SD.WH_DB_DESC,SD.IS_GLOBAL FROM SYSADM.USER_WH SU INNER JOIN  SYSADM.DB_CONFIG SD ON SU.WH_DB_ID = SD.WH_DB_ID WHERE USER_ID = '{0}'", userID);
            return DBHelper.ExecuteDataSet(CommandType.Text, StrSql.ToString());
        }

        public int InsertRoleFunction(DataBase dataBase, DbTransaction tran, RoleFunctionInfo FunctionInfo)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = this.Set_RoleFunction_Parameters(FunctionInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_ROLE_FUNCTION, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertRoleOperation(DataBase dataBase, DbTransaction tran, RoleOperationInfo OperationInfo)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = this.Set_RoleOperation_Parameters(OperationInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_ROLE_OPERATION, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_RoleFunction_Parameters(RoleFunctionInfo roleFunctionInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { new SqlParameter(this.ROLE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.FUNCTION_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20) };
            paramArray[0].Value = roleFunctionInfo.RoleID;
            paramArray[1].Value = roleFunctionInfo.FunctionID;
            paramArray[2].Value = roleFunctionInfo.CreatedBy;
            paramArray[3].Value = roleFunctionInfo.UpdatedBy;
            return paramArray;
        }

        private SqlParameter[] Set_RoleOperation_Parameters(RoleOperationInfo roleOperationInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { new SqlParameter(this.ROLE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.OPERATION_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20) };
            paramArray[0].Value = roleOperationInfo.RoleID;
            paramArray[1].Value = roleOperationInfo.OperationID;
            paramArray[2].Value = roleOperationInfo.CreatedBy;
            paramArray[3].Value = roleOperationInfo.UpdatedBy;
            return paramArray;
        }
    }
}

